
//-------------------------------
// TABLE 1
//-------------------------------

clear
use masterdata_policing.dta

gen vcrime_tot = murders + agg_assault + forc_rape + robbery
gen vcrime_clear = murders_clear + agg_assault_clear + forc_rape_clear + robbery_clear
gen vcrimeclearrate = vcrime_clear/vcrime_tot
gen pcrime_tot = burglary + larcenytheft + mvt
gen pcrime_clear = burglary_clear + larceny_clear + mvt_clear
gen pcrimeclearrate = pcrime_clear/pcrime_tot


gen youthratio = (pop15to17+pop18to24+pop25to34)/totalpopulation
gen blackratio = black/totalpopulation
gen lessthanhighratio = lessthanhigh/popover25
gen unempratio = unemployed/totalpopulation

encode state, gen(state2)

by fipsstate fipscounty, sort: gen countyid = 1 if _n == 1
replace countyid = sum(countyid)

gen lpop = ln(totalpopulation +1)
gen lincome = ln(medianincome + 1)

gen polbudgetpc = totalbudget/population
gen totpolemppc =(fullswornofficer + fullcivilianemp + ptimeswornofficer + ptimecivilianemp)/population
gen swpolemppc = (fullswornofficer + ptimeswornofficer)/population
gen swfullpolemppc = fullswornofficer/population 

// Change variable unit as %
replace vcrimeclearrate = vcrimeclearrate*100
replace pcrimeclearrate = pcrimeclearrate*100
replace policebudget = policebudget*100
replace youthratio = youthratio*100
replace blackratio = blackratio*100
replace lessthanhighratio = lessthanhighratio*100
replace unempratio = unempratio*100
replace gini = gini*100

macro define demographic "lpop youthratio blackratio lessthanhighratio unempratio gini lincome"

gen lvcrime_tot = ln(vcrime_tot +1)
gen lpcrime_tot = ln(pcrime_tot+1)
gen ltotcrime = ln(vcrime_tot + pcrime_tot +1)
gen lvcrime_clear = ln(vcrime_clear +1)
gen lpcrime_clear = ln(pcrime_clear+1)

sum fullswornofficer if population <= 28010 & year == 2012
sum fullswornofficer if population > 28010 & year == 2012

* regression

eststo clear
eststo: quietly reg  vcrimeclearrate pctfinesandforfeits i.year, cl(geo_fips)
eststo: quietly areg vcrimeclearrate pctfinesandforfeits lvcrime_tot lpcrime_tot policebudget swfullpolemppc $demographic i.year, a(countyid) cl(geo_fips)
eststo: quietly areg vcrimeclearrate pctfinesandforfeits lvcrime_tot lpcrime_tot policebudget swfullpolemppc $demographic i.year if population <= 28010, a(countyid) cl(geo_fips)
eststo: quietly areg vcrimeclearrate pctfinesandforfeits lvcrime_tot lpcrime_tot policebudget swfullpolemppc $demographic i.year if population > 28010, a(countyid)  cl(geo_fips)
eststo: quietly reg  pcrimeclearrate pctfinesandforfeits i.year, cl(geo_fips)
eststo: quietly areg pcrimeclearrate pctfinesandforfeits lvcrime_tot lpcrime_tot policebudget swfullpolemppc $demographic i.year, a(countyid) cl(geo_fips)
esttab, star(* 0.10 ** 0.05 *** 0.01) ar2
esttab using countyfe.tex, star(* 0.10 ** 0.05 *** 0.01) ar2 replace

